var express = require('express');
var app = express();
// 配置解析表单数据的中间件
app.use(express.urlencoded({ extended: false }))
// 一定要在路由之前，配置 cors 这个中间件，从而解决接口跨域的问题
const cors = require('cors')
app.use(cors())
//设置跨域访问
app.all('*', function(req, res, next) {
	res.header("Access-Control-Allow-Origin", "*");
	res.header("Access-Control-Allow-Headers", "X-Requested-With");
	res.header("Access-Control-Allow-Methods", "PUT,POST,GET,DELETE,OPTIONS");
	res.header("X-Powered-By", ' 3.2.1');
	res.header("Content-Type", "application/json;charset=utf-8");
	next();
});

const mysql=require('mysql')

var coonnection=mysql.createConnection({
    host:'localhost',
    user:'root',
    password:'root',
    database:'book_node'
})
coonnection.connect(function(err){
    if(err){
        console.log("connect is not good"+err.stack)
        return ;
    }
    console.log("connect is ok:   "+coonnection.threadId)
})
var data
//查找
app.get('/123', function(req, res) {
	coonnection.query('select * from book',(err,result)=>{
		    if(err) throw err
			data=result
			res.status(200),
			res.json(data)
		})
});
// //用于向数据库添加数据
var seachSql='SELECT * FROM book WHERE name LIKE "%?%"'
var seachSqlDate=[]
//搜索
app.get('/seach', function(req, res) {
	seachSqlDate=Object.values(req.query)
	seachSqlDate[0].substring(1,seachSqlDate.length-1);
	coonnection.query(`SELECT * FROM book WHERE name LIKE "%${seachSqlDate[0]}%"
	 or user LIKE "%${seachSqlDate[0]}%" or gread LIKE "%${seachSqlDate[0]}%"
	 or type LIKE "%${seachSqlDate[0]}%" or price LIKE "%${seachSqlDate[0]}%"
	`,(err,result)=>{
		res.status(200),
		res.json(result)
	})
});
// //用于向数据库添加数据
var addSql='INSERT INTO book(name,user,gread,type,price) VALUES(?,?,?,?,?)'
var addSqlDate=[]
//添加
app.get('/add', function(req, res) {
	addSqlDate=Object.values(req.query)
	// res.status(200),
	// res.json(data)
	if(addSqlDate!=[]){
		coonnection.query(addSql,addSqlDate,(err,result)=>{
		    if(err) throw err
		    console.log('添加成功')
		})
	}
});
// //用于向数据库添加数据
var deleteSql='DELETE  FROM book WHERE id=?'
var deleteSqlDate=0
//删除
app.get('/delete', function(req, res) {
	console.log(req.query);
	deleteSqlDate=Object.values(req.query)
	// res.status(200),
	// res.json(data)
	if(deleteSqlDate!=0){
		coonnection.query(deleteSql,deleteSqlDate,(err,result)=>{
		    if(err) throw err
		    console.log('删除成功')
		})
	}
});

// //用于向数据库添加数据
var editSql='update book set name=?,user=?,gread=?,type=?,price=? WHERE id=?'
var editSqlDate=[]
//修改
app.get('/edit', function(req, res) {
	console.log(req.query);
	editSqlDate=Object.values(req.query)
	let id = editSqlDate[0]
	editSqlDate.shift()
	editSqlDate.push(id)
	console.log(editSqlDate);
	// res.status(200),
	// res.json(data)
	if(editSqlDate!=[]){
		coonnection.query(editSql,editSqlDate,(err,result)=>{
		    if(err) throw err
		    console.log('修改成功')
		})
	}
});
//配置服务端口
var server = app.listen(3000, function() {
	var host = server.address().address;
	var port = server.address().port;
	console.log('Example app listening at http://%s:%s', host, port);
})
